using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for Pet
/// </summary>
public class PetDAO : DBConnection
{
    #region sql command
    string SQL_SELECT_ID = "select @@IDENTITY from Pets";
    string SQL_SELECT_ALL = "select * from Pets";
    string sQL_SELECT_BY_PRICE = "select * from Pets where Price>=@lowPrice and Price<@highPrice ";
    //string SQL_SELECT_NEW_PETS = "select top 10 * from Pets order by Petid desc";
    string SQL_SELECT_BY_ID = "select * from Pets where PetID=@id";
    string SQL_INSERT = "insert into Pets([Name], Height, Weight, Price, AvailbleNumber,ImageURL,ItemTypeID, CategoryID, Info,ModifiedDate ) values(@name, @height, @weight, @price, @availableNumber,@imageURL,@itemTypeID, @categoryID, @info,@modifiedDate)";
    string SQL_UPDATE = "update Pets set [Name] = @name, Height = @height, Weight = @weight, Price = @price, AvailbleNumber = @availableNumber,ImamgeURL=@imageURL, ItemTypeID=@itemTypeID, CategoryID=@categoryID,Info=@info,ModifiedDate=@modifiedDate where PetID = @petId";
    string SQL_DELETE = "delete from Pets where PetID = @petId ";
    string SQL_SP_SELECT_PET = "sp_SelectPets";
    string SQL_UPDATE_SALE_OFF_FOR_PET = "update Pets set SaleOff=@saleOff where PetID=@petId";
    string SQL_SELECT_BEST_SELLER = "select * from vwPetsBestSellet";
    string SQL_SELECT_NEW_PETS = "SELECT PetID, Name, Height, Weight, Price, AvailbleNumber, ImageURL, ItemTypeID, CategoryID, Info, SaleOff, ModifiedDate FROM  Pets where ModifiedDate >@modifiedDate";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public PetDAO()
    {
    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }        
    }
    #endregion
    public Pet GetNewPet()
    {
        Pet pet = new Pet();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_NEW_PETS, conn);
           
            if (Connect())
            {
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    pet.ID = dr.GetInt32(0);
                    pet.Name = dr.GetString(1);
                    pet.Height = (float)dr.GetDouble(2);
                    pet.Weight = (float)dr.GetDouble(3);
                    pet.Price = dr.GetDecimal(4);
                    pet.Quantity = dr.GetInt32(5);
                    pet.ImageURL = dr.GetString(6);
                    pet.TypeID = dr.GetInt32(7);
                    pet.Category.ID = dr.GetInt32(8);
                    pet.Information = dr.GetString(9);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return pet;
    }

    public Pet GetPet(int id)
    {
        Pet pet = new Pet();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    pet.ID = dr.GetInt32(0);
                    pet.Name = dr.GetString(1);
                    pet.Height = (float)dr.GetDouble(2);
                    pet.Weight = (float)dr.GetDouble(3);
                    pet.Price = dr.GetDecimal(4);
                    pet.Quantity = dr.GetInt32(5);
                    pet.ImageURL = dr.GetString(6);
                    pet.TypeID = dr.GetInt32(7);
                    pet.Category.ID = dr.GetInt32(8);
                    pet.Information = dr.GetString(9);
                }
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return pet;
    }
    #region methods
    public IList<Pet> GetPets(int currentPage, int pageSize,ref int total)
    {
        IList<Pet> list = new List<Pet>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SP_SELECT_PET, conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
               
                while (dr.Read())
                {
                    Pet pet = new Pet();
                    pet.ID = dr.GetInt32(1);
                    pet.Name = dr.GetString(2);
                    pet.Height =(float) dr.GetDouble(3);
                    pet.Weight = (float)dr.GetDouble(4);
                    pet.Price = dr.GetDecimal(5);
                    pet.Quantity = dr.GetInt32(6);
                    pet.ImageURL = dr.GetString(7);
                    pet.TypeID = dr.GetInt32(8);
                    pet.Category.ID = dr.GetInt32(9);
                    pet.Information = dr.GetString(10);
                    if(!dr.IsDBNull(11)) pet.SaleOff =(float)dr.GetDouble(11);
                    list.Add(pet);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public IList<Pet> GetBessSellerPets(int currentPage, int pageSize, ref int total)
    {
        IList<Pet> list = new List<Pet>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BEST_SELLER, conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();

                while (dr.Read())
                {
                    Pet pet = new Pet();
                    pet.ID = dr.GetInt32(1);
                    pet.Name = dr.GetString(2);
                    pet.Height = (float)dr.GetDouble(3);
                    pet.Weight = (float)dr.GetDouble(4);
                    pet.Price = dr.GetDecimal(5);
                    pet.Quantity = dr.GetInt32(6);
                    pet.ImageURL = dr.GetString(7);
                    pet.TypeID = dr.GetInt32(8);
                    pet.Category.ID = dr.GetInt32(9);
                    pet.Information = dr.GetString(10);
                    if (!dr.IsDBNull(11)) pet.SaleOff = (float)dr.GetDouble(11);
                    list.Add(pet);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public IList<Pet> GetNewPets(int currentPage, int pageSize, ref int total)
    {
        IList<Pet> list = new List<Pet>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_NEW_PETS, conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();

                while (dr.Read())
                {
                    Pet pet = new Pet();
                    pet.ID = dr.GetInt32(1);
                    pet.Name = dr.GetString(2);
                    pet.Height = (float)dr.GetDouble(3);
                    pet.Weight = (float)dr.GetDouble(4);
                    pet.Price = dr.GetDecimal(5);
                    pet.Quantity = dr.GetInt32(6);
                    pet.ImageURL = dr.GetString(7);
                    pet.TypeID = dr.GetInt32(8);
                    pet.Category.ID = dr.GetInt32(9);
                    pet.Information = dr.GetString(10);
                    if (!dr.IsDBNull(11)) pet.SaleOff = (float)dr.GetDouble(11);
                    list.Add(pet);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public IList<Pet> GetPetByName(int currentPage, int pageSize,string name,decimal lowPrice,decimal highPrice ,ref int total)
    {
        IList<Pet> list = new List<Pet>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPetsByName", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@name",SqlDbType.NVarChar);
            command.Parameters.Add("@lowPrice", SqlDbType.Money);
            command.Parameters.Add("@highPrice", SqlDbType.Money);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@name"].Value = "%" + name + "%";
            command.Parameters["@lowPrice"].Value = lowPrice;
            command.Parameters["@highPrice"].Value = highPrice;

            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();

                while (dr.Read())
                {
                    Pet pet = new Pet();
                    pet.ID = dr.GetInt32(1);
                    pet.Name = dr.GetString(2);
                    pet.Height = (float)dr.GetDouble(3);
                    pet.Weight = (float)dr.GetDouble(4);
                    pet.Price = dr.GetDecimal(5);
                    pet.Quantity = dr.GetInt32(6);
                    pet.ImageURL = dr.GetString(7);
                    pet.TypeID = dr.GetInt32(8);
                    pet.Category.ID = dr.GetInt32(9);
                    pet.Information = dr.GetString(10);
                    if (!dr.IsDBNull(11)) pet.SaleOff = (float)dr.GetDouble(11);
                    list.Add(pet);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public IList<Pet> GetPetsByCategory(int currentPage, int pageSize, int categoryID,ref int total)
    {
        IList<Pet> list = new List<Pet>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPetsByCategory", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@categoryID",SqlDbType.Int);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@categoryID"].Value = categoryID;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Pet pet = new Pet();
                    pet.ID = dr.GetInt32(1);
                    pet.Name = dr.GetString(2);
                    pet.Height = (float)dr.GetDouble(3);
                    pet.Weight = (float)dr.GetDouble(4);
                    pet.Price = dr.GetDecimal(5);
                    pet.Quantity = dr.GetInt32(6);
                    pet.ImageURL = dr.GetString(7);
                    pet.TypeID = dr.GetInt32(8);
                    pet.Category.ID = dr.GetInt32(9);
                    pet.Information = dr.GetString(10);
                    if (!dr.IsDBNull(11)) pet.SaleOff = (float)dr.GetDouble(11);
                    list.Add(pet);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public int InsertPet(Pet pet)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT, conn);
            cmd.Parameters.AddWithValue("@name", pet.Name);
            cmd.Parameters.AddWithValue("@height", pet.Height);
            cmd.Parameters.AddWithValue("@weight", pet.Weight);
            cmd.Parameters.AddWithValue("@price", pet.Price);
            cmd.Parameters.AddWithValue("@availableNumber", pet.Quantity);
            cmd.Parameters.AddWithValue("@imageURL", pet.ImageURL);
            cmd.Parameters.AddWithValue("@itemTypeID",pet.TypeID);
            cmd.Parameters.AddWithValue("@categoryID", pet.Category.ID);
            cmd.Parameters.AddWithValue("@info", pet.Information);
            cmd.Parameters.AddWithValue("@ModifiedDate", pet.ModifiedDate);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                cmd.CommandText = SQL_SELECT_ID;
                pet.ID = int.Parse(cmd.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    public int UpdatePet(Pet pet)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE, conn);
            
            cmd.Parameters.AddWithValue("@name", pet.Name);
            cmd.Parameters.AddWithValue("@height", pet.Height);
            cmd.Parameters.AddWithValue("@weight", pet.Weight);
            cmd.Parameters.AddWithValue("@price", pet.Price);
            cmd.Parameters.AddWithValue("@availableNumber", pet.Quantity);
            cmd.Parameters.AddWithValue("@imageURL", pet.ImageURL);
            cmd.Parameters.AddWithValue("@itemTypeID", pet.TypeID);
            cmd.Parameters.AddWithValue("@categoryID", pet.Category.ID);
            cmd.Parameters.AddWithValue("@info", pet.Information);
            cmd.Parameters.AddWithValue("@ModifiedDate", pet.ModifiedDate);
            cmd.Parameters.AddWithValue("@petId", pet.ID);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    public int UpdateSaleOff(int id, float saleOff)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE_SALE_OFF_FOR_PET, conn);
            cmd.Parameters.AddWithValue("@saleOff", saleOff);
            cmd.Parameters.AddWithValue("@petId", id);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    public int DeletePet(Pet pet)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE, conn);
            cmd.Parameters.AddWithValue("@petId", pet.ID);         
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }
    #endregion
}
